﻿CREATE PROCEDURE [dbo].[Report_InventoryOfNotAnalyzedArticles]
	 @RangeReviewId int
AS
	
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
declare @totalCoverage INT = @totalStores * @totalMonths

SELECT
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Delist
	, ar.IsDelisted
	, ar.IsNew
	, parentCat.Name as Category
	, ISNULL(SUM(inv.Cost * ex.Rate) ,0) as InventoryCost
	, ISNULL(SUM(sales.Sales * ex.Rate) ,0) as Sales
FROM 
	(select * from Articles where RangeReviewId = @RangeReviewId
		except
			select distinct ar.*
			from Articles ar WITH (NOLOCK)
			inner join ArticleSales sales WITH (NOLOCK) on 
				sales.ArticleId = ar.Id
				and	sales.StoreId in (select StoreId from RangeReviewStores WITH (NOLOCK) where RangeReviewId = @RangeReviewId and IsExcluded = 0)
			where ar.RangeReviewId = @RangeReviewId) ar --Articulos que no entran en la muestra del RR.
	INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id and rr.Id = @RangeReviewId
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
	INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id
	INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id
	INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId	
	LEFT JOIN Inventories inv WITH (NOLOCK) on inv.ArticleId = ar.Id AND inv.Date = (select MAX(Date) from Inventories WITH (NOLOCK))
	LEFT JOIN ( 
				select ArticleId, SUM(Sales) as Sales
				from ArticleSales s  WITH (NOLOCK)
				where s.Date = (select MAX(Date) from Inventories WITH (NOLOCK)) 
					  and s.StoreId not in (select StoreId from RangeReviewStores WITH (NOLOCK) where RangeReviewId = @RangeReviewId and IsExcluded = 0)
			    group by s.ArticleId
			   ) as sales on sales.ArticleId = ar.Id
	LEFT JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = inv.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where
	ar.RangeReviewId = @RangeReviewId 
	and parentCat.ParentId = 6
Group by
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Delist
	, ar.IsDelisted
	, ar.IsNew
	, parentCat.Name
	, co.IsVATIncluded